/*
helferlein.com ( http://www.helferlein.com )
Michael Tobisch

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and 
to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions 
of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
DEALINGS IN THE SOFTWARE.
*/
using System;
using System.Configuration;
using System.Data;
using DotNetNuke;
using DotNetNuke.Common.Utilities;
using DotNetNuke.Framework.Providers;
using Microsoft.ApplicationBlocks.Data;

namespace helferlein.DNN.Modules.Form.Data
{
    /// -----------------------------------------------------------------------------
    /// <summary>
    /// The SqlDataProvider class is a SQL Server implementation of the abstract DataProvider
    /// </summary>
    /// <remarks>
    /// </remarks>
    /// <history>
    /// </history>
    /// -----------------------------------------------------------------------------
   public class SqlDataProvider : DataProvider
   {
#region Private Members
      private const string PROVIDER_TYPE = "data";
      private const string MODULE_QUALIFIER = "helferlein_";
      private ProviderConfiguration _providerConfiguration = null;
      private string _connectionString;
      private string _providerPath;
      private string _objectQualifier;
      private string _databaseOwner;
#endregion

#region Protected Properties
      /// <summary>
      /// Gets the Provider Configuration
      /// </summary>
      protected ProviderConfiguration ProviderConfiguration
      {
         get
         {
            if (_providerConfiguration == null)
               _providerConfiguration = ProviderConfiguration.GetProviderConfiguration(PROVIDER_TYPE);
            return _providerConfiguration;
         }
      }
#endregion

#region Private Methods
      /// <summary>
      /// Returns the fully qualified name
      /// </summary>
      private string GetFullyQualifiedName(string objectName)
      {
         return this.DatabaseOwner + this.ObjectQualifier + MODULE_QUALIFIER + objectName;
      }

      /// -----------------------------------------------------------------------------
      /// <summary>
      /// Gets the value for the field or DbNull if field has "null" value
      /// </summary>
      /// <param name="field">The field to evaluate</param>
      /// <returns>The value of the field or DbNull</returns>
      /// -----------------------------------------------------------------------------
      private object GetNull(object field)
      {
         return Null.GetNull(field, DBNull.Value);
      }
#endregion

#region Public Properties
      /// <summary>
      /// Gets and sets the connection string
      /// </summary>
      public string ConnectionString
      {
         get { return _connectionString; }
      }

      /// <summary>
      /// Gets and sets the Provider path
      /// </summary>
      public string ProviderPath
      {
         get { return _providerPath; }
      }

      /// <summary>
      /// Gets and sets the Object qualifier
      /// </summary>
      public string ObjectQualifier
      {
         get { return _objectQualifier; }
      }

      /// <summary>
      /// Gets and sets the database ownere
      /// </summary>
      public string DatabaseOwner
      {
         get { return _databaseOwner; }
      }
#endregion

#region Constructor
      /// <summary>
      /// Constructs new SqlDataProvider instance
      /// </summary>
      public SqlDataProvider()
      {
         try
         {
            Provider provider = (Provider)ProviderConfiguration.Providers[ProviderConfiguration.DefaultProvider];

            _connectionString = Config.GetConnectionString();

            _providerPath = provider.Attributes["providerPath"];

            _objectQualifier = provider.Attributes["objectQualifier"];
            if ((_objectQualifier != string.Empty) && (!(_objectQualifier.EndsWith("_"))))
               _objectQualifier += "_";

            _databaseOwner = provider.Attributes["databaseOwner"];
            if ((_databaseOwner != string.Empty) && (!(_databaseOwner.EndsWith("."))))
               _databaseOwner += ".";
         }
         catch (Exception ex)
         {
            throw ex;
         }
      }
#endregion

#region FormItem Methods
      public override IDataReader GetFormItem(int formItemID)
      {
         return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("FormItemGetByFormItemID"), new object[] { formItemID });
      }

      public override IDataReader GetFormItems(int moduleID)
      {
         return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("FormItemGetByModuleID"), new object[] { moduleID });
      }

      public override int AddFormItem(int moduleID, int formItemType, int? formItemRepeatColumns, int sortOrder, bool formItemRequired, bool formItemOther, int? width, int? height, int? formItemLinkTarget, string validationExpression, string aquarium)
      {
         try
         {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("FormItemInsert"), new object[] { moduleID, formItemType, this.GetNull(formItemRepeatColumns), sortOrder, formItemRequired, this.GetNull(formItemOther), this.GetNull(width), this.GetNull(height), this.GetNull(formItemLinkTarget), this.GetNull(validationExpression), aquarium }));
         }
         catch (Exception ex)
         {
            throw ex;
         }
      }

      public override void DeleteFormItem(int formItemID)
      {
         SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("FormItemDelete"), new object[] { formItemID });
      }

      public override void UpdateFormItem(int moduleID, int formItemID, int formItemType, int? formItemRepeatColumns, int sortOrder, bool formItemRequired, bool formItemOther, int? width, int? height, int? formItemLinkTarget, string validationExpression, string aquarium)
      {
         SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("FormItemUpdate"), new object[] { moduleID, formItemID, formItemType, this.GetNull(formItemRepeatColumns), sortOrder, formItemRequired, this.GetNull(formItemOther), this.GetNull(width), this.GetNull(height), this.GetNull(formItemLinkTarget), this.GetNull(validationExpression), aquarium });
      }

      public override int GetNextSortValue(int moduleID)
      {
         return (int)SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("FormItemGetNextSortValueByModuleID"), new object[] { moduleID });
      }

      public override void RequireItem(int formItemID)
      {
         SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("FormItemRequire"), new object[] { formItemID });
      }

      public override void MoveTop(int formItemID)
      {
         SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("FormItemMoveTop"), new object[] { formItemID });
      }

      public override void MoveBottom(int formItemID)
      {
         SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("FormItemMoveBottom"), new object[] { formItemID });
      }

      public override void Move(int formItemID, int direction)
      {
         SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("FormItemMove"), new object[] { formItemID, direction });
      }
#endregion

#region formSubmission Methods
      public override IDataReader GetFormSubmission(int formSubmissionID)
      {
         return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("FormSubmissionGetByformSubmissionID"), new object[] { formSubmissionID });
      }

      public override IDataReader GetFormSubmissions(int moduleID)
      {
         return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("FormSubmissionGetByModuleID"), new object[] { moduleID });
      }

      public override IDataReader GetCurrentFormSubmissions(int moduleID, DateTime formSubmissionDate)
      {
          return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("FormSubmissionGetCurrentByModuleID"), new object[] { moduleID, formSubmissionDate });
      }

      public override IDataReader GetPublicFormSubmissions(int moduleID, DateTime formSubmissionDate)
      {
          return (IDataReader)SqlHelper.ExecuteReader(ConnectionString, GetFullyQualifiedName("FormSubmissionGetPublicByModuleID"), new object[] { moduleID, formSubmissionDate });
      }

      public override int AddFormSubmission(int moduleID, DateTime formSubmissionDate, string formSubmission, bool formSubmissionApproved, string formSubmissionIPAddress, string formSubmissionLocale)
      {
         return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, GetFullyQualifiedName("FormSubmissionInsert"), new object[] { moduleID, formSubmissionDate, formSubmission, formSubmissionApproved, formSubmissionIPAddress, formSubmissionLocale }));
      }

      public override void DeleteFormSubmission(int formSubmissionID)
      {
         SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("FormSubmissionDelete"), new object[] { formSubmissionID });
      }

      public override void DeleteAllFormSubmissions(int moduleID)
      {
         SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("FormSubmissionDeleteAll"), new object[] { moduleID });
      }

      public override void UpdateFormSubmission(int moduleID, int formSubmissionID, DateTime formSubmissionDate, string formSubmission, bool formSubmissionApproved, string formSubmissionIPAddress, string formSubmissionLocale)
      {
         SqlHelper.ExecuteNonQuery(ConnectionString, GetFullyQualifiedName("FormSubmissionUpdate"), new object[] { moduleID, formSubmissionID, formSubmissionDate, formSubmission, formSubmissionApproved, formSubmissionIPAddress, formSubmissionLocale });
      }
#endregion
   }
}